package org.radsimplified.genapp.metadata;

import java.io.File;
import java.io.IOException;
import java.math.BigInteger;

import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

public class Loader {
	
	public static void load(String metadataPath) throws NumberFormatException, IOException {
		
		File file = new File(metadataPath);
		SpreadSheet metadata = SpreadSheet.createFromFile(file);
		
		// App
		Sheet sheet = metadata.getSheet("App");
		App app = new App();
		app.setGroupId(getText(sheet, "A2"));
		app.setArtifactId(getText(sheet, "B2"));
		app.setName(getText(sheet, "C2"));
		app.setRestricted(getBoolean(sheet, "D2"));
		app.setDatePattern(getText(sheet, "E2"));
		app.setDateTimePattern(getText(sheet, "F2"));
		app.setTimePattern(getText(sheet, "G2"));
		app.setDb(getText(sheet, "H2"));
		app.setDbUser(getText(sheet, "I2"));
		app.setDbPassword(getText(sheet, "J2"));
		app.setEmailHost(getText(sheet, "K2"));
		app.setEmailId(getText(sheet, "L2"));
		app.setEmailPassword(getText(sheet, "M2"));
		app.setMaxUploadSize(Integer.parseInt(sheet.getCellAt("N2").getTextValue().trim()));
		app.setPageSize(getByte(sheet, "O2"));

		// Properties
		sheet = metadata.getSheet("Properties");
		Property property;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			property = new Property();
			property.setName(sheet.getCellAt("A" + i).getTextValue().trim());
			property.setValue(sheet.getCellAt("B" + i).getTextValue().trim());
			property.setDataType(sheet.getCellAt("C" + i).getTextValue().trim());
			app.addProperty(property);
		}
		
		// Messages
		sheet = metadata.getSheet("Messages");
		Message message;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			message = new Message();
			message.setName(sheet.getCellAt("A" + i).getTextValue().trim());
			message.setMessage(sheet.getCellAt("B" + i).getTextValue().trim());
			app.addMessage(message);
		}

		// Entities
		sheet = metadata.getSheet("Entities");
		BizEntity entity;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			entity = new BizEntity();
			entity.setName(sheet.getCellAt("A" + i).getTextValue().trim());
			entity.setPlural(sheet.getCellAt("B" + i).getTextValue().trim());
			entity.setTableName(sheet.getCellAt("C" + i).getTextValue().trim());
			entity.setTitle(sheet.getCellAt("D" + i).getTextValue().trim());
			// Identifier column is set later
			entity.setOrderBy(sheet.getCellAt("F" + i).getTextValue().trim());
			entity.setCustomView(getBoolean(sheet, "G" + i));
			entity.setCustomEdit(getBoolean(sheet, "H" + i));
			entity.setCustomList(getBoolean(sheet, "I" + i));
			entity.setModule(sheet.getCellAt("J" + i).getTextValue().trim());
			app.addModule(entity.getModule()); // Adds distinct module names
			
			app.addEntity(entity);
		}
		
		// Unique Keys
		sheet = metadata.getSheet("UniqueKeys");
		UniqueKey uniqueKey;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			
			uniqueKey = new UniqueKey();
			entity = app.getEntity(sheet.getCellAt("A" + i).getTextValue().trim());
			entity.addUniqueKey(uniqueKey);
			
			uniqueKey.setColumns(getText(sheet, "B" + i));
		}

		// Operations
		sheet = metadata.getSheet("Operations");
		Operation operation;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			
			operation = new Operation();
			entity = app.getEntity(sheet.getCellAt("A" + i).getTextValue().trim());
			entity.addOperation(operation);
			
			operation.setName(sheet.getCellAt("B" + i).getTextValue().trim());
			operation.setLabel(sheet.getCellAt("C" + i).getTextValue().trim());
			operation.setVisibleInList(getBoolean(sheet, "D" + i));
		}

		// Attributes
		sheet = metadata.getSheet("Attributes");
		Attribute attribute;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			
			attribute = new Attribute();
			entity = app.getEntity(sheet.getCellAt("A" + i).getTextValue().trim());
			entity.addAttribute(attribute);
			
			attribute.setName(sheet.getCellAt("B" + i).getTextValue().trim());
			attribute.setDataType(sheet.getCellAt("C" + i).getTextValue().trim());
			attribute.setDefaultValue(getText(sheet, "D" + i));
			attribute.setNullable(getBoolean(sheet, "E" + i));
			attribute.setUnigue(getBoolean(sheet, "F" + i));
			attribute.setPattern(sheet.getCellAt("G" + i).getTextValue().trim());
			attribute.setMin(getBigInteger(sheet, "H" + i));
			attribute.setMax(getBigInteger(sheet, "I" + i));
			attribute.setRelatedEntity(app.getEntity(sheet.getCellAt("J" + i).getTextValue().trim()));
			attribute.setFetchType(sheet.getCellAt("K" + i).getTextValue().trim());
			attribute.setCascadeType(sheet.getCellAt("L" + i).getTextValue().trim());
			attribute.setMappedBy(sheet.getCellAt("M" + i).getTextValue().trim());
			attribute.setOrderBy(sheet.getCellAt("N" + i).getTextValue().trim());
			attribute.setVisible(getBoolean(sheet, "O" + i));
			attribute.setSensitive(getBoolean(sheet, "P" + i));
			attribute.setVisibleInList(getBoolean(sheet, "Q" + i));
			attribute.setSearchable(getBoolean(sheet, "R" + i));
			attribute.setEditable(getBoolean(sheet, "S" + i));
			attribute.setBlankNull(getBoolean(sheet, "T" + i));
			attribute.setComputed(getBoolean(sheet, "U" + i));
			attribute.setLabel(sheet.getCellAt("V" + i).getTextValue().trim());
			attribute.setListLabel(sheet.getCellAt("W" + i).getTextValue());
			attribute.setHelpText(sheet.getCellAt("X" + i).getTextValue().trim());
			attribute.setYearRange(sheet.getCellAt("Y" + i).getTextValue().trim());
			attribute.setRows(getByte(sheet, "Z" + i));
			attribute.setCols(getByte(sheet, "AA" + i));
			attribute.setColumnName(sheet.getCellAt("AB" + i).getTextValue().trim());
			
			if (attribute.getRelatedEntity() != null &&
				attribute.getDataType().equals("ManyToOneSelect") &&
			   (attribute.getEditable() || attribute.getSearchable())) {
				entity.addSelectableEntity(attribute.getRelatedEntity());
				if (attribute.getEditable())
					entity.addSelectableEditableEntity(attribute.getRelatedEntity());
				if (attribute.getSearchable())
					entity.addSelectableSearchableEntity(attribute.getRelatedEntity());
			}
				
		}
		
		// Set identifier column of entities
		sheet = metadata.getSheet("Entities");
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			String identifierColumn = sheet.getCellAt("E" + i).getTextValue().trim();
			if (!identifierColumn.equals("")) {
				entity = app.getEntity(sheet.getCellAt("A" + i).getTextValue().trim());
				attribute = entity.getAttribute(identifierColumn);
				entity.setIdentifierColumn(attribute);
			}
		}
		
		// Set select options of entities
		sheet = metadata.getSheet("SelOptions");
		SelOption selOption;
		for (int i = 2; !getText(sheet, "A" + i).equals(""); i++) {
			
			selOption = new SelOption();
			entity = app.getEntity(sheet.getCellAt("A" + i).getTextValue().trim());
			attribute = entity.getAttribute(sheet.getCellAt("B" + i).getTextValue().trim());
			attribute.addSelOption(selOption);

			selOption.setValue(sheet.getCellAt("C" + i).getTextValue().trim());
			selOption.setLabel(sheet.getCellAt("D" + i).getTextValue().trim());
		}
		
		App.setApp(app);
	}
	
	private static String getText(Sheet sheet, String address) {
		try {
			return sheet.getCellAt(address).getTextValue().trim().replace("“", "\"").replace("”", "\"");
		} catch (IndexOutOfBoundsException e) {
			return "";
		}
	}
	
	private static BigInteger getBigInteger(Sheet sheet, String address) {
		try {
			return new BigInteger(sheet.getCellAt(address).getTextValue().trim());
		} catch (Exception e) {
			return null;
		}
	}
	
	private static Byte getByte(Sheet sheet, String address) {
		try {
			return Byte.parseByte(sheet.getCellAt(address).getTextValue().trim());
		} catch (Exception e) {
			return null;
		}
	}
	
	private static Boolean getBoolean(Sheet sheet, String address) {
		try {
			String value = sheet.getCellAt(address).getTextValue().trim();
			switch (value.charAt(0)) {
				case 'T': case 't': case 'Y': case 'y': return true;
				case 'F': case 'f': case 'N': case 'n': return false;
				default: return null;
			}
		} catch (Exception e) {
			return null;
		}
		
	}
	
}
